from Accounting.CashFlow.SubRed import *
from Accounting.Entity.Feeder import *
from Accounting.Entity.Client import *
from Accounting.Entity.Tree import EntityTree
from Accounting.Entity.Utils import *
from Error.ExtendError import EntityNA
from Config.ImportConfig import *

from DataAccess.DBConnFactory import DBConnFactory
from string import Template
import csv
import os.path
from datetime import date, datetime, timedelta


#perfee_adj_conf format:
#(parent_feetp4_perfadj, )

#rebate_conf format:
#(parent_feetp4_rebate,rebate_ratio, )
def open_day_create_client(client_id, perfee_adj_conf=None, rebate_conf=None):
	conn = DBConnFactory().get_db_connection('ACCOUNTING')
	
	sql_text = '''insert into fee_conf
				(entity,class,subtype,sequence,parent_feetp4_perfadj,
				parent_feetp4_rebate,rebate_ratio,is_redem,schedule,curncy)
				values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10)'''
				
	if perfee_adj_conf:
		v = (client_id,'ClientFloatFeeAdj','perf_fee_adj',2,'inv_perf_fee',None,None,'T','ME_Float','USD')
		conn.cursor().execute(sql_text, v)
		
	if rebate_conf:
		v = (client_id,'ClientFixedFeeRebate','mgt_fee_rebate',1,None,'inv_mgt_fee',0.5,'T','ME_Fixed','USD')
		conn.cursor().execute(sql_text, v)
		
	conn.commit()
	

def check_client_fee_conf(client_id, feeder_id):
	if feeder_id=='SC028_US' or feeder_id=='SC028_NONUS':
		conn = DBConnFactory().get_db_connection('ACCOUNTING')
		cursor = conn.cursor()
		cursor.execute("select * from fee_conf where entity='"+client_id+"'")
		r = cursor.fetchone()
		if not r or len(r)==0:
			if client_id=='HML035574' or client_id=='HML035561':
				open_day_create_client(client_id, True, True)	
			else:
				open_day_create_client(client_id, True, False)
			
	
def open_day_purchase(ref_date, client_id, cash_amt, currency, 
					feeder_id=None, master_id=None, 
					nav_date=None):
	
	if not nav_date:
		nav_date = ref_date
	
	check_client_fee_conf(client_id, feeder_id)
					
	client = EntityTree().find_node(client_id)
	if not client:		
		parent_feeder = EntityTree().find_node(feeder_id)
		if not parent_feeder:
			parent_master = EntityTree().find_node(master_id)
			assert parent_master			
			#update to runtime object 
			parent_feeder = Feeder(feeder_id, parent_master)
			parent_master.add_child(feeder_id, parent_feeder)
			
		client = Client(client_id, parent_feeder)		
		parent_feeder.add_child(client_id, client)
		
	conv_by = share_amt_conv_type(nav_date, client)
	#print 'Active Subscription @',ref_date.isoformat(), 'conv_by=', conv_by
	action = Subscription(ref_date, cash_amt, currency, conv_by, client, 'active', nav_date)
	client.purchase_fund(action)
	

def open_day_redemption(ref_date, client_id, 
					cash_amt, currency, share_amt, ByCash, 
					corresponding_subscription_id, 
					nav_date=None):
	
	if not nav_date:
		nav_date = ref_date
					
	client = EntityTree().find_node(client_id)
	if client:
		conv_by = share_amt_conv_type(nav_date, client)
		
		if ByCash:
			type = RedemptionByAmount
			amount = cash_amt
		else:
			type = RedemptionByShare
			amount = share_amt
		#print 'Active Redemption @',ref_date.isoformat(), 'conv_by=', conv_by	
		action = Redemption(ref_date, amount, currency, type, 
						corresponding_subscription_id, conv_by, client, 'active', 
						nav_date)
		client.redeming_fund(action)
	else:
		raise EntityNA(), ('Client entity not exist, id='+client_id)
		


def open_day_redemption_by_feeder(ref_date, feeder_id, cash_amt, currency):
	feeder = EntityTree().find_node(feeder_id)
	if feeder:
		assert feeder.parent
		master = feeder.parent
		action = Redemption(ref_date, cash_amt, currency, RedemptionByAmount, 
						None, 'NAV', feeder, 'active')
		master.redeming_fund(action)
	else:
		raise EntityNA(), ('Feeder entity not exist, id='+feeder_id)
	
	
def open_day_purchase_by_feeder(ref_date, feeder_id, cash_amt, currency):
	feeder = EntityTree().find_node(feeder_id)
	if feeder:
		assert feeder.parent
		master = feeder.parent
		action = Subscription(ref_date, cash_amt, currency, 'NAV', feeder, 'active')
		master.purchase_fund(action)
	else:
		raise EntityNA(), ('Feeder entity not exist, id='+feeder_id)
		
		
def is_feeder_entity(entity_id):
	entity = EntityTree().find_node(entity_id)	
	if not entity:
		return False
		
	return (not is_client_entity(entity))	
		


def settle_sub_red_operations(ref_date):
	#First Step
	settle_sub_by_equity_operation(ref_date)

	conn = DBConnFactory().get_db_connection('ACCOUNTING')
	cursor = conn.cursor()
	sql_tpl = Template('''select client_id,feeder_id2,'SUB',
						sum(abs(cash_amt)),curncy,0,'',ref_date,'','',nav_date
					from official_share_chg
					where transaction2='SUB'
					  and ref_date=TO_DATE('${DATE}','yyyy-mm-dd')
					group by client_id,feeder_id2,curncy,ref_date,nav_date
				UNION
					select client_id,feeder_id2,'RED',
						decode(method,'BYCASH',abs(cash_amt),0.0),curncy,
						decode(method,'BYSHARE',abs(share_amt),0.0),
						red_ref_id,red_ref_date,method,detail,nav_date
					from official_share_chg
					where transaction2='RED'
					  and ref_date=TO_DATE('${DATE}','yyyy-mm-dd')
					''')
	
	cursor.execute(sql_tpl.substitute(DATE=ref_date.isoformat()))
	lines = cursor.fetchall()
	for line in lines:
		id = line[0]
		parent = line[1]
		trans_type = line[2]
		cash_amt = line[3]
		curncy = line[4]	
		share_amt = line[5]
		sub_ref_id = line[6]
		sub_ref_date = line[7]
		trans_by = line[8]
		detail = line[9]		
		nav_date = line[10].date() if line[10] else ref_date

		if is_feeder_entity(id):		
			if trans_type=='SUB':
				open_day_purchase_by_feeder(ref_date, id, cash_amt, curncy)
			else:
				open_day_redemption_by_feeder(ref_date, id, cash_amt, curncy)
		
		else:		
			if trans_type=='SUB':
				#add master info for fund inception subscription
				master_id = parent.split('_')[0]					
				open_day_purchase(ref_date, id, cash_amt, curncy, parent, master_id, nav_date)
			else:	
				corr_sub_id = find_redemption_corr_sub_id(sub_ref_date, id, detail, sub_ref_id)
				#print 'debug, corr_sub_id =',corr_sub_id
				open_day_redemption(ref_date, id, cash_amt, curncy, share_amt, (trans_by=='BYCASH'), corr_sub_id, nav_date)



def settle_sub_by_equity_operation(ref_date):
	conn = DBConnFactory().get_db_connection('ACCOUNTING')
	cursor = conn.cursor()
	sql_tpl = Template('''insert into position@pke_db
					(ref_date,portfolio_id,ticker,security_type,amount,avg_cost_price,price_currency)
					select ref_date,substr(feeder_id2,0,instr(feeder_id2,'_')-1),
						ticker,security_type,amount,avg_cost_price,price_currency
					from official_sub_by_equity
					where transaction2='SUB'
					  and ref_date=TO_DATE('${DATE}','yyyy-mm-dd')''')
	cursor.execute(sql_tpl.substitute(DATE=ref_date.isoformat()))
	
	sql_tpl = Template('''insert into position@pke_db
					(ref_date,portfolio_id,ticker,security_type,amount,avg_cost_price,price_currency)
					select ref_date,substr(feeder_id2,0,instr(feeder_id2,'_')-1),
						'9999999','CASH',-sum(amount*avg_cost_price) cash_amt,1.0,price_currency
					from official_sub_by_equity
					where transaction2='SUB'
					  and ref_date=TO_DATE('${DATE}','yyyy-mm-dd')
					group by ref_date,feeder_id2,price_currency''')
	cursor.execute(sql_tpl.substitute(DATE=ref_date.isoformat()))
	
	sql_tpl = Template('''select client_id,feeder_id2,sum(amount*avg_cost_price) cash_amt,price_currency
					from official_sub_by_equity
					where transaction2='SUB'
					  and ref_date=TO_DATE('${DATE}','yyyy-mm-dd')
					group by client_id,feeder_id2,price_currency''')
	cursor.execute(sql_tpl.substitute(DATE=ref_date.isoformat()))
	for sub in cursor.fetchall():
		client_id = sub[0]
		feeder_id = sub[1]
		cash_amt = sub[2]
		currency = sub[3]
		master_id = feeder_id.split('_')[0]
		
		open_day_purchase(ref_date, client_id, cash_amt, currency, feeder_id, master_id)

	

def find_redemption_corr_sub_id(ref_sub_date, client_id, detail, corr_sub_id=None):
	if corr_sub_id and len(corr_sub_id):
		print '    info, Step 8: assign the ref_sub_id by operator.'
		return corr_sub_id
	
	conn = DBConnFactory().get_db_connection('ACCOUNTING')
	cursor = conn.cursor()
	if ref_sub_date:  #for SC028_NONUS,SC028_US
		ref_sub_date = ref_sub_date.date()
		detail_criteria = ''
		if detail:
			detail_criteria = "and detail='"+detail+"'"
		sql_tpl2 = Template('''select id from share_chg
							where ref_date=TO_DATE('${DATE}','yyyy-mm-dd')
							and type='subscription'
							and child='${CLIENT}' 
							${DETAIL_CRITERIA}
							order by amount desc''')
		sql_text = sql_tpl2.substitute(DATE=ref_sub_date.isoformat(), 
									CLIENT=client_id,
									DETAIL_CRITERIA=detail_criteria)
									
		cursor.execute(sql_text)
		r = cursor.fetchall()
		if not r or len(r)<1:
			print '    error, Step 8: can not find corresponding subscription, id=', id, 
			return 
		elif r and len(r)>1:
			print '    warn, Step 8: have more than one corresponding subscription, id=', id
			print '    warn, Step 8: corresponding share_chg rows:', r
			corr_sub_id = raw_input('    warn, Step 8: please determine the corr_id:')
			corr_sub_id = int(corr_sub_id)
		else:
			corr_sub_id = r[0][0]
		
		return corr_sub_id
		
	else:
		sql_tpl2 = Template('''select * from 
						(select id from 
						feeder_remain_share
						where child='${CLIENT}' 
						order by ref_date) 
						where rownum<=1''')
						
		cursor.execute(sql_tpl2.substitute(CLIENT=client_id))
		t = cursor.fetchone()
		if (not t) or len(t)==0:
			print '    error, Step 8: can not find corresponding subscription, id=', id, 
			return
		else:
			corr_sub_id = t[0]
		
		return corr_sub_id

	
		
def deprecated_settle_sub_red_operations(ref_date, file_path = None):
	if not file_path:
		file_path = SUBRED_RD_FILE
	
	if(not os.path.exists(file_path)):
		print '    warn, Step 8: sub_red record file NOT EXIST.'
		return 
			
	csv_reader = csv.reader(open(file_path, 'rb'))
	if not csv_reader:
		#raise RuntimeError, ('Can open repo trades csv file.')
		print '    error, Step 8: open sub_red record file FAILED.'
		return
	
	first_line = True
	for line in csv_reader:
		if first_line: #skip the first title line
			first_line = False
			continue
		
		#trade_record = (ref_date,
						# entity_id,
						# parent_id,
						# trans_type,
						# trans_by,
						# cash_amt,
						# currency,
						# shares,
						# red_corr_date[optional])
						
		rd_date = datetime.strptime(line[0],'%Y-%m-%d').date()
		if ref_date>rd_date:
			continue
		elif ref_date<rd_date:
			return
		else:
			id = line[1]
			parent = line[2]
			trans_type = line[3]
			trans_by = line[4]

			if is_feeder_entity(id):
				cash_amt = abs(float(line[5]))
				curncy = line[6]
				
				if trans_type=='SUB':
					#print cash_amt, curncy
					#print "open_day_purchase_by_feeder(ref_date, id, cash_amt, curncy)"
					open_day_purchase_by_feeder(ref_date, id, cash_amt, curncy)
				else:
					#print cash_amt, curncy
					#print "open_day_redemption_by_feeder(ref_date, id, cash_amt, curncy)"
					open_day_redemption_by_feeder(ref_date, id, cash_amt, curncy)					
			else:
				if trans_type=='SUB':
					cash_amt = abs(float(line[5]))
					curncy = line[6]
					#print cash_amt, curncy
					#print "open_day_purchase(ref_date, id, cash_amt, curncy, parent)"
					
					#add master info for fund inception subscription
					master_id = parent.split('_')[0]					
					open_day_purchase(ref_date, id, cash_amt, curncy, parent, master_id)					
				else:
					conn = DBConnFactory().get_db_connection('ACCOUNTING')
					cursor = conn.cursor()
					sql_tpl = Template('''select id from share_chg
										where ref_date=TO_DATE('${DATE}','yyyy-mm-dd')
										and type='subscription'
										and child='${CLIENT}' 
										order by amount desc''')
					sub_date = datetime.strptime(line[8],'%Y-%m-%d').date()				
					cursor.execute(sql_tpl.substitute(DATE=sub_date.isoformat(), CLIENT=id))
					r = cursor.fetchall()
					if not r or len(r)<1:
						print '    error, Step 8: can not find corresponding subscription, ', id
					elif r and len(r)>1:
						print '    warn, Step 8: have more than one corresponding subscription, ', id
					else:
						corr_sub_id = r[0][0]
					
						cash_amt = 0.0
						curncy = ''
						share_amt = 0
						if trans_by=='BYCASH':
							cash_amt = abs(float(line[5]))
							curncy = line[6]
						else:				
							curncy = line[6]						
							share_amt = abs(float(line[7]))
							
						#print cash_amt, curncy, share_amt	
						#print "open_day_redemption(ref_date, id, cash_amt, curncy, share_amt, (trans_by=='BYCASH'), corr_sub_id)"						
						open_day_redemption(ref_date, id, cash_amt, curncy, share_amt, (trans_by=='BYCASH'), corr_sub_id)
						
	